﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data.SqlClient;
using VPLink2013.Framework;

namespace VPLink2013.Webparts
{
    public class NbMembres : WebpartVPLink
    {
        #region get HTML
        /// <summary>
        /// Returns the HTML code rendered by this web part
        /// </summary>
        /// <returns>HTL code as string</returns>
        protected override string GetHtml()
        {
            ContextTrace.PrefixFileName = "webpart NbMembres";
            string strHtml = ContextTrace.PrefixFileName;

            string strDetail = "";
            if (this.Title.StartsWith("Detail"))
            {
                strDetail = "Detail";
                ContextTrace.PrefixFileName += strDetail;
            }
            string strHtmlFileCache = pathCache + @"\NbMembres\NbMembres" + strDetail + ".htm";

            try
            {
                FileSerialization.CheckFileCacheExists(strHtmlFileCache, 15);
                strHtml = "";

                if (System.IO.File.Exists(strHtmlFileCache))
                {
                    strHtml = System.IO.File.ReadAllText(strHtmlFileCache);
                    sbHtml.Append(strHtml);
                    ContextTrace.AddTrace("File cache found !");
                }

                if (string.IsNullOrEmpty(strHtml.Replace("\r\n", "").Trim()))
                {
                    SPSecurity.RunWithElevatedPrivileges
                    (delegate()
                    {
                        if (strDetail == "Detail")
                        {
                            //ProcessDetailNbMembres();
                        }
                        else
                        {
                            //ProcessTotalNbMembres();
                        }
                    });
                }
            }
            catch (System.Exception exc)
            {
                ContextTrace.AddTrace("Erreur action/fill global : " + exc.Message + "/" + exc.StackTrace);
            }
            strHtml = sbHtml.ToString();
            TraceFile(strHtmlFileCache, strHtml);

            return strHtml;
        }
        #endregion get HTML

        private int ProcessDiffNbMembres()
        {

            string strSql = "select top 2 DateExtraction, TotalNbMembres from dbo.TotalNbMembres order by DateExtraction desc";
            SqlConnection conn = VPLink2013SqlHelper.GetConnection(connectionString);
            SqlCommand cmd = VPLink2013SqlHelper.GetCommand(conn, strSql);
            SqlDataReader Reader = cmd.ExecuteReader();

            int intTotalNbMembresPrec = 0;
            int intTotalNbMembresCrt = 0;

            int iCrt = 0;
            while (Reader.Read())
            {
                if (!Reader.IsDBNull(1))
                {
                    if (iCrt == 1)
                    {
                        intTotalNbMembresPrec = Reader.GetInt32(1);
                    }
                    else
                    {
                        intTotalNbMembresCrt = Reader.GetInt32(1);
                    }
                }
                iCrt += 1;
            }
            VPLink2013SqlHelper.CloseReader(conn, Reader);

            ContextTrace.AddTrace("intTotalNbMembresCrt : " + intTotalNbMembresCrt);
            ContextTrace.AddTrace("intTotalNbMembresPrec : " + intTotalNbMembresPrec);

            return intTotalNbMembresCrt - intTotalNbMembresPrec;
        }

        private void ProcessTotalNbMembres()
        {
            string strSql = "SELECT SUM(NbMembres) AS Total, MAX(DateExtraction) AS MaxDate FROM Membres";
            SqlConnection conn = VPLink2013SqlHelper.GetConnection(connectionString);
            SqlCommand cmd = VPLink2013SqlHelper.GetCommand(conn, strSql);
            SqlDataReader Reader = cmd.ExecuteReader();

            int intNbMembres = 0;
            while (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    ContextTrace.AddTrace("GetFieldType : " + Reader.GetFieldType(0));
                    intNbMembres = Reader.GetInt32(0);
                }
            }
            VPLink2013SqlHelper.CloseReader(conn, Reader);

            sbHtml.AppendLine("<div class=\"bloc_right-column\">");
            sbHtml.AppendLine("<h1>Nombre de membres</h1>");
            sbHtml.AppendLine("<div id=\"NbMembres\">");
            sbHtml.AppendLine("<div class=\"column-content\" id=\"counter\">" + intNbMembres.ToString() + "</div>");
            sbHtml.AppendLine("<script type=\"text/javascript\">");
            sbHtml.AppendLine("    var updatedate = \"" + DateTime.Now.Date.AddHours(9).ToString("yyyy-MM-dd hh:mm:ss") + "\";");
            sbHtml.AppendLine("    var updatevalue = " + intNbMembres.ToString() + ";");
            sbHtml.AppendLine("    var nbbyday = " + ProcessDiffNbMembres().ToString() + ";");
            sbHtml.AppendLine("    var delay = 86400000;");
            sbHtml.AppendLine("</script>");
            sbHtml.AppendLine("<script type=\"text/javascript\" src=\"/_layouts/15/VPLink2013/js/counter01.js\"></script>");
            sbHtml.AppendLine("</div>");
            sbHtml.AppendLine("<span id=\"NbMenbres_info\">Actualisation du nombre de membres en temps réel</span>");
            sbHtml.AppendLine("<a id=\"link_MoreNb\" href='" + ParamSharepointSite + "/Pages/Chiffres.aspx'  onclick=\"javascript:_paq.push(['trackPageView', 'Nombre de Membres/Plus de chiffres']);\">Voir plus de chiffres</a>");
            sbHtml.AppendLine("</div>");
            sbHtml.AppendLine("</div>");
        }

        private void ProcessDetailNbMembres()
        {
            StringBuilder sbStart = new StringBuilder();

            sbStart.Append("<div id=\"nbMembers\" class=\"figures_table\">");
            sbStart.Append("<table>");
            sbStart.Append("<caption>");
            sbStart.Append("<span>1</span>Nombre de membres par pays");
            sbStart.Append("</caption>");
            sbStart.Append("<colgroup>");
            sbStart.Append("<col class=\"col1\" />");
            sbStart.Append("<col class=\"col2\" />");
            sbStart.Append("</colgroup>");
            sbStart.Append("<thead>");
            sbStart.Append("<tr>");
            sbStart.Append("<th>Pays</th>");
            sbStart.Append("<th>Nombre de membres</th>");
            sbStart.Append("</tr>");
            sbStart.Append("<thead>");
            sbStart.Append("<tbody>");

            string strSql = "SELECT Pays, NbMembres, DateExtraction FROM Membres ORDER BY IdPays";
            SqlConnection conn = VPLink2013SqlHelper.GetConnection(connectionString);
            SqlCommand cmd = VPLink2013SqlHelper.GetCommand(conn, strSql);
            SqlDataReader Reader = cmd.ExecuteReader();
            int intTotal = 0;
            string strCrt = "2";
            string strDate = "";

            while (Reader.Read())
            {
                string strPays = "";
                if (!Reader.IsDBNull(0))
                {
                    strPays = Reader.GetString(0);
                }
                int intNbMembres = 0;
                if (!Reader.IsDBNull(1))
                {
                    intNbMembres = Reader.GetInt32(1);
                }
                if (!Reader.IsDBNull(2))
                {
                    strDate = Reader.GetDateTime(2).ToString("dd MMMM yyyy");
                }
                sbHtml.Append("     <tr class=\"tableLine" + strCrt + "\">");
                sbHtml.Append("         <td>");
                sbHtml.Append("<nobr>" + strPays + "</nobr>");
                sbHtml.Append("         </td>");
                sbHtml.Append("         <td align='right'>");
                sbHtml.Append(intNbMembres.ToString("#,##0"));
                sbHtml.Append("         </td>");
                sbHtml.Append("     </tr>");
                intTotal += intNbMembres;

                if (strCrt == "1")
                {
                    strCrt = "2";
                }
                else
                {
                    strCrt = "1";
                }
            }
            VPLink2013SqlHelper.CloseReader(conn, Reader);

            sbStart.Append("     <tr class=\"tableLine1\">");
            sbStart.Append("         <td class=\"total\">");
            sbStart.Append("TOTAL");
            sbStart.Append("         </td>");
            sbStart.Append("         <td class=\"nbTotal\" align='right'>");
            sbStart.Append(intTotal.ToString("#,##0"));
            sbStart.Append("         </td>");
            sbStart.Append("     </tr>");


            sbHtml.Append("</tbody>");
            sbHtml.Append("</table>");
            sbHtml.Append("</font></span>");

            sbHtml.Insert(0, sbStart.ToString());
        }

        private string GetPays(string pays)
        {
            return "<span style='font-family:Symbol;font-size:11pt;'>·</span>&nbsp;"
                + "<span style='font-family:'Verdana','sans-serif';color:black'>" + GetFontSize(pays) + "</span>";
        }

        private string GetFontSize(string strHtml)
        {
            return "<font size='2'>" + strHtml + "</font>";
        }
    }
}
